﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace FYPDemo
{
    public class Member
    {
        public static int registernewMember(string name, string pw, string email, string phone, string address, string postcode, string state, int role, string imagePath, int securityQ, string answer)
        {
            int result = 0;

            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "addNewMember";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@Username", SqlDbType.VarChar, 50));
                cmd.Parameters["@Username"].Value = name;

                cmd.Parameters.Add(new SqlParameter("@Password", SqlDbType.VarChar, 50));
                cmd.Parameters["@Password"].Value = pw;

                cmd.Parameters.Add(new SqlParameter("@Email", SqlDbType.VarChar, 50));
                cmd.Parameters["@Email"].Value = email;

                cmd.Parameters.Add(new SqlParameter("@Phone", SqlDbType.VarChar, 20));
                cmd.Parameters["@Phone"].Value = phone;

                cmd.Parameters.Add(new SqlParameter("@Address", SqlDbType.VarChar, 50));
                cmd.Parameters["@Address"].Value = address;

                cmd.Parameters.Add(new SqlParameter("@PostCode", SqlDbType.VarChar, 10));
                cmd.Parameters["@PostCode"].Value = postcode;

                cmd.Parameters.Add(new SqlParameter("@State", SqlDbType.VarChar, 20));
                cmd.Parameters["@State"].Value = state;

                cmd.Parameters.Add(new SqlParameter("@Role", SqlDbType.Int));
                cmd.Parameters["@Role"].Value = role;

                cmd.Parameters.Add(new SqlParameter("@ImagePath", SqlDbType.VarChar, 100));
                cmd.Parameters["@ImagePath"].Value = imagePath;

                cmd.Parameters.Add(new SqlParameter("@SecurityQ", SqlDbType.Int));
                cmd.Parameters["@SecurityQ"].Value = securityQ;

                cmd.Parameters.Add(new SqlParameter("@Answer", SqlDbType.VarChar, 100));
                cmd.Parameters["@Answer"].Value = answer;

                result = cmd.ExecuteNonQuery();
            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                if (con != null)
                { con.Close(); }
            }

            return result;
        }

        public static int checkUserName(string name)
        {
            int result = 0;

            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();

            conndb db = new conndb();

            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "checkUsername";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@Username", SqlDbType.VarChar, 50));
                cmd.Parameters["@Username"].Value = name;

                da.SelectCommand = cmd;
                da.Fill(dt);

                result = dt.Rows.Count;
            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                con.Close();
            }

            return result;
        }

        public static DataTable showMemberDetails(string name)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();

            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "showMemberDetails";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@Name", SqlDbType.VarChar, 50));
                cmd.Parameters["@Name"].Value = name;

                da.SelectCommand = cmd;
                da.Fill(dt);
            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                con.Close();
            }

            return dt;
        }

        public static int updateMemberDetails(string name, string email, string phone, string address, string postcode, string state, string imagePath, string vendorCode)
        {
            int result = 0;

            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "updateMemberProfile";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@Name", SqlDbType.VarChar, 50));
                cmd.Parameters["@Name"].Value = name;

                cmd.Parameters.Add(new SqlParameter("@Email", SqlDbType.VarChar, 50));
                cmd.Parameters["@Email"].Value = email;

                cmd.Parameters.Add(new SqlParameter("@Phone", SqlDbType.VarChar, 20));
                cmd.Parameters["@Phone"].Value = phone;

                cmd.Parameters.Add(new SqlParameter("@Address", SqlDbType.VarChar, 100));
                cmd.Parameters["@Address"].Value = address;

                cmd.Parameters.Add(new SqlParameter("@Postcode", SqlDbType.VarChar, 10));
                cmd.Parameters["@Postcode"].Value = postcode;
                
                cmd.Parameters.Add(new SqlParameter("@State", SqlDbType.VarChar, 20));
                cmd.Parameters["@State"].Value = state;

                cmd.Parameters.Add(new SqlParameter("@ImagePath", SqlDbType.VarChar, 100));
                cmd.Parameters["@ImagePath"].Value = imagePath;

                cmd.Parameters.Add(new SqlParameter("@VendorCode", SqlDbType.VarChar, 4));
                cmd.Parameters["@VendorCode"].Value = vendorCode;
                
                result = cmd.ExecuteNonQuery();
            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                if (con != null)
                { con.Close(); }
            }

            return result;
        }

        public static DataTable verifyOldPassword(string name, string oldPassword)
        {            
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();

            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "verifyOldPassword";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@Name", SqlDbType.VarChar, 50));
                cmd.Parameters["@Name"].Value = name;

                cmd.Parameters.Add(new SqlParameter("@OldPassword", SqlDbType.VarChar, 50));
                cmd.Parameters["@OldPassword"].Value = oldPassword;

                da.SelectCommand = cmd;
                da.Fill(dt);
            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                con.Close();
            }

            return dt;
        }

        public static int updatePassword(string name, string newPassword)
        {
            int result = 0;
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();

            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "updatePassword";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@Name", SqlDbType.VarChar, 50));
                cmd.Parameters["@Name"].Value = name;

                cmd.Parameters.Add(new SqlParameter("@NewPassword", SqlDbType.VarChar, 50));
                cmd.Parameters["@NewPassword"].Value = newPassword;

                result = cmd.ExecuteNonQuery();
            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                con.Close();
            }

            return result;
        }       
    }
}